在参加公司的应届生技术成长项目时，参与了一个在线课堂项目的迭代开发: http://learn.didichuxing.com，其中一个需求与实现逻辑简述如下：基于已有的观看历史功能，实现人气值功能，若用户是第一次访问该课程则新增一条访问记录且将该课程人气值加1，若以前已经访问过该课程则只更新该课程最后访问时间（人气值可简单理解为点赞数）。

这是非常常见的业务逻辑：若不存在相关记录则插入，若存在则更新或采取其它操作。在本次项目中原实现观看历史的代码逻辑为先查询表中是否存在欲插入的数据（即是否访问过该课程），若不存在则插入新记录否则更新最后访问时间，即先SELECT然后INSERT或UPDATE。

然而这种做法并不见得效率有多高，还需要考虑事务问题，除了常见的SELECT->INSERT/UPDATE，本文整理了常用来实现这种或类似逻辑的语句，介绍一下它们的用法与注意点，并进行了简单的性能实验与对比：

INSERT ON DUPLICATE KEY UPDATE语句：

该语句将先尝试插入指定的记录，若遇到主键（或唯一键）冲突将会尝试更新冲突的记录，若更新又遇到冲突将返回错误。

实例：

建表语句：

sql

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `cnt` int(11) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_name` (`name`)
) ENGINE=InnoDB;

执行以下语句：

sql

1 2	INSERT INTO t (name) VALUES ('zhangsan') ON DUPLICATE KEY UPDATE cnt = cnt + 1; INSERT INTO t (name) VALUES ('lisi') ON DUPLICATE KEY UPDATE cnt = cnt + 1;

因表中还没有数据，顺利写入数据，此时以上语句等效于：

sql

1 2	INSERT INTO t (name) VALUES ('zhangsan'); INSERT INTO t (name) VALUES ('lisi');

再次执行

sql

1	INSERT INTO t (name) VALUES ('zhangsan') ON DUPLICATE KEY UPDATE cnt = cnt + 1;

这时由于存在唯一键name，将产生冲突，name=’zhangsan’的记录将被更新，此时以上语句又等效于：

sql

1	UPDATE t SET cnt = cnt + 1 WHERE name = 'zhangsan' LIMIT 1;

在使用INSERT ON DUPLICATE KEY UPDATE 语句时可以使用VALUES()函数从INSERT列表中取值，这在多行插入时尤其实用，如：

sql

1	INSERT INTO t1 (a, b, c) VALUES (2, 3, 5), (3, 6, 9) ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b);

等价于以下两条语句：

sql

1 2	INSERT INTO t1 (a, b, c) VALUES (2, 3, 5) ON DUPLICATE KEY UPDATE c = 5; INSERT INTO t1 (a, b, c) VALUES (3, 6, 9) ON DUPLICATE KEY UPDATE c = 9;

若要引用当前记录行的值则直接使用列名即可。

影响行数：

影响行数为0：欲插入的记录与当前已有的记录存在冲突，且欲更新的值与当前记录的值相同。
影响行数为1：新插入了一条记录。
影响行数为2：欲插入的记录与当前已有的记录存在冲突，将当前记录更新为指定值。

注意：

与多行记录冲突只会更新一条记录：
对于上面的数据，如果执行

sql

1	INSERT INTO t (id, name) VALUES (2, 'zhangsan') ON DUPLICATE KEY UPDATE cnt = cnt + 1;

欲插入的数据将会和表中的两条数据冲突，那这两条记录都会被更新吗：

可以看见返回的影响行数为2，查出来的数据显示只有id=2的一行数据数据被更新了，即若待插入的数据与现有的多条记录冲突，也只会有一条记录被给更新，在使用时应尽量避免这种情况。

若UPDATE也发生了冲突将会返回错误：
若INSERT发生冲突，UPDATE又发生冲突，该语句将返回错误，如果添加IGNORE选项将忽略该错误并记录到WARNING中：
可能造成自增字段的空洞：
如表中存在自增字段，如主键id，执行INSERT ON DUPLICATE KEY UPDATE 即使没有新增记录或影响行数为0也将使auto_increment值增加。

REPLACE INTO语句：

REPLACE处理逻辑：先尝试插入一条新记录，如果因主键冲突或与其他唯一索引冲突则删除原有的有冲突的记录，然后再次尝试插入新纪录。适用于用新纪录覆盖原来记录值的情况，如文章开头提到的课程最后访问时间的逻辑。（注：存储引擎也可能在某些情况下用UPDATE代替DELETE+INSERT，但效果上是一样的）

实例：

同样是操作上面的表t，执行以下语句：

sql

1	REPLACE INTO t (name) VALUES ('zhangsan');

影响行数：

REPLACE语句返回的影响行数是删除和新增的行数总和。

影响行数为1：新增1行，没有被删除的行。
影响行数大于1：在新增记录之前有1行或多行记录被删除，注意如果表中有多个唯一键且待新增的行与原来的多条记录有冲突，REPLACE将会删除多条记录再插入新记录。

根据返回的影响行数就可以很容易地判断是仅仅新增记录还是替换了记录。

注意：

在REPLACE语句中无法使用当前已有记录行的值:
如执行下面的语句多次，其结果均一样，cnt一直是默认值加1，而不是预想的一直累加。
sql
1
REPLACE INTO t (name, cnt) VALUES ('zhangsan', cnt + 1);

可能会删除多条记录
若表中有多个唯一键约束，REPLACE将可能会删除多行记录，涉及多个唯一键时若不指定列的值而使用默认值，多次执行也会造成之前的记录被删除。如表中列a, b和c都存在唯一键约束，执行下面的操作将会删除所有有冲突的记录行：

如上图，执行REPLACE into t(a, b, c) values(1, 2, 3)会将原id=1,2,3的记录删除后再插入新记录，故返回的影响行数为4。
表中应当存在唯一键约束：
如果REPLACE语句不会触发插入冲突，则在效果上和常规的INSERT语句相同。
在某些情况下（如非主键冲突且一行记录内的冲突只有1个）REPLACE操作在binlog中记录的是(删除加)更新操作，会造成主备库的AUTO_INCREMENT不同。

BASIC INSERT语句：

若遇主键或唯一键冲突，则会执行失败。

INSERT IGNORE语句：

若欲插入的记录与当前表中的主键或唯一键键冲突，将忽略INSERT语句在执行过程中的错误，新纪录不会被插入表中，可用于避免重复记录的插入。忽略的错误将被记录到WARNING中，通过SHOW WARNINGS语句可查看。

实验测试：

本实验均是基于Innodb存储引擎。
实验共测试了6种语句：

INSERT INTO
INSERT IGNORE INTO
先SELECT再根据结果判断插入或更新
INSERT INTO 若冲突再 UPDATE
REPLACE INTO
INSERT ON DUPLICATE KEY UPDATE

建表语句如下，id为主键，a为唯一键

sql

CREATE TABLE t_xx (
	id int NOT NULL AUTO_INCREMENT,
	a int NOT NULL DEFAULT 0,
	cnt int NOT NULL DEFAULT 1,
	PRIMARY KEY (id),
	UNIQUE uinq_a (a)
) ENGINE = InnoDB;

插入语句如下：

sql

INSERT INTO t_insert (a) VALUES (floor(rand() * scope));

INSERT IGNORE INTO t_insert_ignore (a) VALUES (floor(rand() * scope));

--先select再update或insert
SET v = floor(rand() * scope) + 1;
IF EXISTS (
	SELECT 1 FROM t_select_then WHERE a = v LIMIT 1 )
	THEN UPDATE t_select_then SET a = a + scope WHERE a = v;
ELSE 
	INSERT INTO t_select_then (a) VALUES (v);
END IF;

--先insert若冲突再update
SET v = floor(rand() * scope) + 1;
INSERT INTO t_insert_then (a) VALUES (v);
如果插入失败
UPDATE t_insert_then SET cnt = cnt + 1 WHERE a = v LIMIT 1;

REPLACE INTO t_replace (a) VALUES (floor(rand() * scope));

INSERT INTO t_duplicate (a) VALUES (floor(rand() * scope)) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

--注：scope为传入参数，即随机数范围，控制插入数据时唯一键的冲突率

通过编写存储过程测试不同运行次数下耗时(每次插入或更新就commit)，最后表里的数据量在运行次数的50%-60%，耗时统计如下：

	运行10W次	运行50W次	运行100W次	运行500W次
INSERT INTO…	48.59s	180.89s	371.69s	30 min 57.46 sec
INSERT IGNORE INTO…	38.20s	182.13s	299.70s	32 min 34.25 sec

SELECT 若不存在则 INSERT 否则 UPDATE	59.33s	310.45s	614.21s	56 min 14.27 sec
INSERT INTO 若冲突再 UPDATE	79.06s	304.80s	743.56s	1 hour 5 min 49.56 sec
REPLACE INTO…	60.07s	307.05	571.11s	57 min 58.02 sec
INSERT ON DUPLICATE KEY UPDATE	58.19s	279.76s	572.71s	51 min 44.54 sec

测试随机函数rand()的消耗：

sql

CREATE PROCEDURE p_test_rand (
	IN count int UNSIGNED, 
	IN scope int UNSIGNED
)
BEGIN
	DECLARE s int UNSIGNED DEFAULT 1;
	DECLARE v int UNSIGNED DEFAULT 0;
	WHILE s <= count DO
	SET v = floor(rand() * scope) + 1;
	SET s = s + 1;
	END WHILE
END;

经测试随机函数rand()的耗时占总耗时的1%左右，且不是实验项的变量，可忽略。

后四项实验是实现的是类似的逻辑，可以看出INSERT ON DUPLICATE KEY UPDATE的耗时是占优势的，使用该语句还可以简化程序中的语言逻辑，保证操作的原子性，甚至被称为8个提升mysql性能的技巧之一。当然使用时也需要注意区分使用场景，如果在操作前已经明确知道应当插入还是更新，则直接INSERT/UPDATE效率更高（如第一项实验数据）。

因本人水平有限，对MySQL的理解还不够深入，难免有疏漏或不当之处，欢迎各位拍砖。